Download the Sql File attached to the
Run Script that is attached to create your Staging tables in the Staging Database. Once ran the tables will be made up of these columns.
*Using SSIS Toolbox, the following components will be drag in the Control Flow Dashboard;
Case Statement to populate Payroll Number:
AgencyName
== "RICHMOND DA" ? "905" : AgencyName == "KINGS
DA" ? "903" : AgencyName == "POLICE DEPARTMENT" ?
"56" : AgencyName == "RICHMOND DA" ? "905" :
AgencyName == "BRONX COMMUNITY BOARD #1" ? "381" :
AgencyName == "TAX COMMISSION" ? "21" : AgencyName ==
"BOARD OF CORRECTION" ? "73" : AgencyName == "BOROUGH
PRESIDENT-STATEN IS" ? "14" : AgencyName == "BRONX
COMMUNITY BOARD #2" ? "382" : AgencyName == "BRONX
COMMUNITY BOARD #3" ? "383" : AgencyName == "BRONX
COMMUNITY BOARD #4" ? "384" : AgencyName == "BRONX
COMMUNITY BOARD #5" ? "385" : AgencyName == "BRONX
COMMUNITY BOARD #6" ? "386" : AgencyName == "BRONX
COMMUNITY BOARD #7" ? "387" : AgencyName == "BRONX
COMMUNITY BOARD #8" ? "388" : AgencyName == "BRONX
COMMUNITY BOARD #9" ? "389" : AgencyName == "BRONX
COMMUNITY BOARD #10" ? "390" : AgencyName == "BRONX
COMMUNITY BOARD #11" ? "391" : AgencyName == "BRONX
COMMUNITY BOARD #12" ? "392" : AgencyName == "BROOKLYN
COMMUNITY BOARD #1" ? "472" : AgencyName == "BROOKLYN
COMMUNITY BOARD #2" ? "472" : AgencyName == "BROOKLYN
COMMUNITY BOARD #3" ? "473" : AgencyName == "BROOKLYN
COMMUNITY BOARD #4" ? "474" : AgencyName == "BROOKLYN COMMUNITY
BOARD #5" ? "475" : AgencyName == "BROOKLYN COMMUNITY BOARD
#6" ? "476" : AgencyName == "BROOKLYN COMMUNITY BOARD
#7" ? "477" : AgencyName == "BROOKLYN COMMUNITY BOARD
#8" ? "478" : AgencyName == "BROOKLYN COMMUNITY BOARD
#9" ? "479" : AgencyName == "BROOKLYN COMMUNITY BOARD
#10" ? "480" : AgencyName == "BROOKLYN COMMUNITY BOARD
#11" ? "481" : AgencyName == "BROOKLYN COMMUNITY BOARD
#12" ? "482" : AgencyName == "BROOKLYN COMMUNITY BOARD
#13" ? "483" : AgencyName == "BROOKLYN COMMUNITY BOARD
#14" ? "484" : AgencyName == "BROOKLYN COMMUNITY BOARD
#15" ? "485" : AgencyName == "BROOKLYN COMMUNITY BOARD
#16" ? "486" : AgencyName == "BROOKLYN COMMUNITY BOARD
#17" ? "487" : AgencyName == "CAMPAIGN FINANCE BOARD"
? "4" : AgencyName == "CONFLICTS OF INTEREST BOARD" ?
"312" : AgencyName == "CIVIL SERVICE COMMISSION" ?
"134" : AgencyName == "CULTURAL AFFAIRS" ? "126"
: AgencyName == "CUNY CENTRAL OFFICE" ? "467" : AgencyName
== "DEPARTMENT OF JUVENILE JUSTICE" ? "130" : AgencyName ==
"EQUAL EMPLOY PRACTICES COMM" ? "133" : AgencyName ==
"QUEENS COMMUNITY BOARD #1" ? "431" : AgencyName ==
"QUEENS COMMUNITY BOARD #2" ? "432" : AgencyName ==
"QUEENS COMMUNITY BOARD #3" ? "433" : AgencyName ==
"QUEENS COMMUNITY BOARD #4" ? "434" : AgencyName ==
"QUEENS COMMUNITY BOARD #5" ? "435" : AgencyName ==
"QUEENS COMMUNITY BOARD #6" ? "436" : AgencyName ==
"QUEENS COMMUNITY BOARD #7" ? "437" : AgencyName ==
"QUEENS COMMUNITY BOARD #8" ? "438" : AgencyName ==
"QUEENS COMMUNITY BOARD #9" ? "439" : AgencyName ==
"QUEENS COMMUNITY BOARD #10" ? "440" : AgencyName ==
"QUEENS COMMUNITY BOARD #11" ? "441" : AgencyName ==
"QUEENS COMMUNITY BOARD #13" ? "443" : AgencyName ==
"QUEENS COMMUNITY BOARD #14" ? "444" : AgencyName ==
"OFFICE OF COLLECTIVE BARGAININ" ? "313" : AgencyName ==
"MUNICIPAL WATER FIN AUTHORITY" ? "185" : AgencyName ==
"MANHATTAN COMMUNITY BOARD #1" ? "341" : AgencyName ==
"MANHATTAN COMMUNITY BOARD #2" ? "342" : AgencyName ==
"MANHATTAN COMMUNITY BOARD #3" ? "343" : AgencyName ==
"MANHATTAN COMMUNITY BOARD #4" ? "344" : AgencyName ==
"MANHATTAN COMMUNITY BOARD #5" ? "345" : AgencyName ==
"MANHATTAN COMMUNITY BOARD #6" ? "346" : AgencyName ==
"MANHATTAN COMMUNITY BOARD #7" ? "347" : AgencyName ==
"MANHATTAN COMMUNITY BOARD #8" ? "348" : AgencyName ==
"MANHATTAN COMMUNITY BOARD #9" ? "349" : AgencyName ==
"MANHATTAN COMMUNITY BOARD #10" ? "350" : AgencyName ==
"MANHATTAN COMMUNITY BOARD #11" ? "351" : AgencyName ==
"MANHATTAN COMMUNITY BOARD #12" ? "352" : AgencyName ==
"NYC DEPT OF VETERANS' SERVICES" ? "63" : AgencyName ==
"HUNTER COLLEGE HIGH SCHOOL" ? "470" : AgencyName ==
"OFFICE OF THE MAYOR" ? "2" : AgencyName == "PUBLIC
ADMINISTRATOR-QUEENS" ? "944" : AgencyName == "RICHMOND
DA" ? "905" : AgencyName == "STATEN ISLAND COMMUNITY BD
#1" ? "491" : AgencyName == "STATEN ISLAND COMMUNITY BD
#2" ? "492" : AgencyName == "STATEN ISLAND COMMUNITY BD
#3" ? "493" : AgencyName == "Spec Narcs-DA" ?
"906" : AgencyName == "TAXI & LIMOUSINE COMMISSION" ?
"156" : AgencyName == "TEACHERS RETIREMENT SYSTEM" ?
"41" : AgencyName == "PUBLIC ADVOCATE" ? "101" :
AgencyName == "PUBLIC SERVICE CORPS" ? "210" : AgencyName
== "QUEENS COMMUNITY BOARD #12" ? "442" : AgencyName ==
"Queens DA" ? "904" : AgencyName == "PUBLIC
ADMINISTRATOR-KINGS" ? "943" : AgencyName == "PUBLIC
ADMINISTRATOR-NEW YORK" ? "941" : AgencyName == "PUBLIC
ADMINISTRATOR-RICHMOND" ? "945" : AgencyName == "POLICE
DEPARTMENT" ? "56" : AgencyName == "PRESIDENT BOROUGH OF
MANHATTAN" ? "10" : AgencyName == "PUBLIC
ADMINISTRATOR-BRONX" ? "942" : AgencyName == "OFFICE OF THE
COMPTROLLER" ? "15" : AgencyName == "OFFICE OF THE
ACTUARY" ? "2" : AgencyName == "PERSONNEL MONITORS" ?
"33" : AgencyName == "OFFICE OF LABOR RELATIONS" ?
"214" : AgencyName == "OFFICE OF MANAGEMENT & BUDGET" ?
"19" : AgencyName == "OFFICE OF THE ACTUARY" ?
"8" : AgencyName == "NYC POLICE PENSION FUND" ?
"256" : AgencyName == "OFF OF PAYROLL ADMINISTRATION" ?
"131" : AgencyName == "OFFICE OF EMERGENCY MANAGEMENT" ?
"17" : AgencyName == "MAYORS OFFICE OF CONTRACT SVCS" ?
"82" : AgencyName == "NYC EMPLOYEES RETIREMENT SYS" ?
"9" : AgencyName == "NYC HOUSING AUTHORITY" ?
"996" : AgencyName == "INDEPENDENT BUDGET OFFICE" ?
"132" : AgencyName == "LANDMARKS PRESERVATION COMM" ?
"136" : AgencyName == "LAW DEPARTMENT" ? "25" :
AgencyName == "Manhattan DA" ? "901" : AgencyName ==
"HOUSING PRESERVATION & DVLPMNT" ? "806" : AgencyName
== "HRA/DEPT OF SOCIAL SERVICES" ? "69" : AgencyName ==
"HUMAN RIGHTS COMMISSION" ? "226" : AgencyName == "DOE
CUSTODIAL PAYROL" ? "202" : AgencyName == "FINANCIAL INFO
SVCS AGENCY" ? "127" : AgencyName == "FIRE DEPARTMENT"
? "57" : AgencyName == "GUTTMAN COMMUNITY COLLEGE" ?
"462" : AgencyName == "DISTRICT ATTORNEY RICHMOND COU" ?
"905" : AgencyName == "DISTRICT ATTORNEY-MANHATTAN" ?
"901" : AgencyName == "DISTRICT ATTORNEY-SPECIAL NARC" ?
"906" : AgencyName == "DEPT. OF HOMELESS SERVICES" ?
"71" : AgencyName == "DISTRICT ATTORNEY KINGS COUNTY" ?
"903" : AgencyName == "DISTRICT ATTORNEY QNS COUNTY" ?
"904" : AgencyName == "DEPT OF RECORDS & INFO SERVICE"
? "860" : AgencyName == "DEPT OF YOUTH & COMM DEV SRVS"
? "261" : AgencyName == "DEPT. OF DESIGN &
CONSTRUCTION" ? "850" : AgencyName == "DEPT OF
HEALTH/MENTAL HYGIENE" ? "816" : AgencyName == "DEPT OF
INFO TECH & TELECOMM" ? "858" : AgencyName == "DEPT OF
PARKS & RECREATION" ? "846" : AgencyName == "DEPT OF ED
PER DIEM TEACHERS" ? "746" : AgencyName == "DEPT OF ED PER
SESSION TEACHER" ? "747" : AgencyName == "DEPT OF
ENVIRONMENT PROTECTION" ? "826" : AgencyName == "DEPT OF ED
HRLY SUPPORT STAFF" ? "745" : AgencyName == "DEPT OF ED
PARA PROFESSIONALS" ? "744" : AgencyName == "DEPT OF ED
PEDAGOGICAL" ? "742" : AgencyName == "DEPARTMENT OF
SANITATION" ? "827" : AgencyName == "DEPARTMENT OF
TRANSPORTATION" ? "841" : AgencyName == "DEPT OF CITYWIDE
ADMIN SVCS" ? "868" : AgencyName == "DEPARTMENT OF EDUCATION
ADMIN" ? "740" : AgencyName == "DEPARTMENT OF FINANCE"
? "836" : AgencyName == "DEPARTMENT OF INVESTIGATION" ?
"32" : AgencyName == "DEPARTMENT OF PROBATION" ?
"781" : AgencyName == "CONSUMER AFFAIRS" ? "866"
: AgencyName == "DEPARTMENT FOR THE AGING" ? "125" :
AgencyName == "DEPARTMENT OF BUILDINGS" ? "810" :
AgencyName == "DEPARTMENT OF BUSINESS SERV." ? "801" :
AgencyName == "DEPARTMENT OF CITY PLANNING" ? "30" :
AgencyName == "DEPARTMENT OF CORRECTION" ? "72" :
AgencyName == "COMMUNITY COLLEGE (HOSTOS)" ? "468" :
AgencyName == "COMMUNITY COLLEGE (KINGSBORO)" ? "465" :
AgencyName == "COMMUNITY COLLEGE (LAGUARDIA)" ? "469" :
AgencyName == "COMMUNITY COLLEGE (MANHATTAN)" ? "466" :
AgencyName == "COMMUNITY COLLEGE (QUEENSBORO)" ? "464" :
AgencyName == "BUSINESS INTEGRITY COMMISSION" ? "831" :
AgencyName == "CITY CLERK" ? "103" : AgencyName ==
"CITY COUNCIL" ? "102" : AgencyName == "CIVILIAN
COMPLAINT REVIEW BD" ? "54" : AgencyName == "COMMUNITY
COLLEGE (BRONX)" ? "463" : AgencyName == "BRONX COMMUNITY
BOARD #4" ? "384" : AgencyName == "Bronx DA" ?
"902" : AgencyName == "BRONX DISTRICT ATTORNEY" ?
"902" : AgencyName == "BROOKLYN COMMUNITY BOARD #15" ?
"485" : AgencyName == "BROOKLYN COMMUNITY BOARD #18" ?
"488" : AgencyName == "ADMIN FOR CHILDREN'S SVCS" ? "67"
: AgencyName == "ADMIN TRIALS AND HEARINGS" ? "820" :
AgencyName == "BOARD OF ELECTION" ? "3" : AgencyName ==
"BOARD OF ELECTION POLL WORKERS" ? "300" : AgencyName ==
"BOROUGH PRESIDENT-BRONX" ? "11" : AgencyName ==
"BOROUGH PRESIDENT-BROOKLYN" ? "12" : AgencyName ==
"BOROUGH PRESIDENT-QUEENS" ? "13" : PayrollNumberData Conversion:
Conditional Split Code:
*Using SSIS Toolbox, the following components will be drag in the Control Flow Dashboard;
Derived Column Code:
Case Statement to populate Payroll
Number
AgencyName == "HRA/DEPT OF SOCIAL SERVICES" ? 69 : AgencyName == "NYC FIRE PENSION FUND" ? 257 : AgencyName == "HOUSING PRESERVATION & DVLPMNT" ? 806 : AgencyName == "GUTTMAN COMMUNITY COLLEGE" ? 462 : AgencyName == "CULTURAL AFFAIRS" ? 126 : AgencyName == "ADMIN FOR CHILDREN'S SVCS" ? 67 : AgencyName == "ADMIN TRIALS AND HEARINGS" ? 820 : AgencyName == "BOARD OF CORRECTION" ? 73 : AgencyName == "BOARD OF CORRECTIONS" ? 73 : AgencyName == "BOARD OF ELECTION" ? 3 : AgencyName == "BOROUGH PRESIDENT-BRONX" ? 11 : AgencyName == "BOROUGH PRESIDENT-BROOKLYN" ? 12 : AgencyName == "BOROUGH PRESIDENT-QUEENS" ? 13 : AgencyName == "BOROUGH PRESIDENT-STATEN IS" ? 14 : AgencyName == "BRONX DISTRICT ATTORNEY" ? 902 : AgencyName == "BUSINESS INTEGRITY COMMISSION" ? 831 : AgencyName == "CITY CLERK" ? 103 : AgencyName == "CITY COUNCIL" ? 102 : AgencyName == "CIVILIAN COMPLAINT REVIEW BD" ? 54 : AgencyName == "COMMUNITY COLLEGE (BRONX)" ? 463 : AgencyName == "COMMUNITY COLLEGE (HOSTOS)" ? 468 : AgencyName == "COMMUNITY COLLEGE (KINGSBORO)" ? 465 : AgencyName == "COMMUNITY COLLEGE (LAGUARDIA)" ? 469 : AgencyName == "COMMUNITY COLLEGE (MANHATTAN)" ? 466 : AgencyName == "CONSUMER AFFAIRS" ? 866 : AgencyName == "DEPARTMENT FOR THE AGING" ? 125 : AgencyName == "DEPARTMENT OF BUILDINGS" ? 810 : AgencyName == "DEPARTMENT OF BUSINESS SERV." ? 801 : AgencyName == "DEPARTMENT OF CITY PLANNING" ? 30 : AgencyName == "DEPARTMENT OF CORRECTION" ? 72 : AgencyName == "DEPARTMENT OF EDUCATION ADMIN" ? 740 : AgencyName == "DEPARTMENT OF FINANCE" ? 836 : AgencyName == "DEPARTMENT OF INVESTIGATION" ? 32 : AgencyName == "DEPARTMENT OF PROBATION" ? 781 : AgencyName == "DEPARTMENT OF SANITATION" ? 827 : AgencyName == "DEPARTMENT OF TRANSPORTATION" ? 841 : AgencyName == "DEPT OF CITYWIDE ADMIN SVCS" ? 868 : AgencyName == "DEPT OF ED PEDAGOGICAL" ? 742 : AgencyName == "DEPT OF ENVIRONMENT PROTECTION" ? 826 : AgencyName == "DEPT OF HEALTH/MENTAL HYGIENE" ? 816 : AgencyName == "DEPT OF INFO TECH & TELECOMM" ? 858 : AgencyName == "DEPT OF PARKS & RECREATION" ? 846 : AgencyName == "DEPT OF RECORDS & INFO SERVICE" ? 860 : AgencyName == "DEPT OF YOUTH & COMM DEV SRVS" ? 261 : AgencyName == "DEPT. OF DESIGN & CONSTRUCTION" ? 850 : AgencyName == "DEPT. OF HOMELESS SERVICES" ? 71 : AgencyName == "DISTRICT ATTORNEY KINGS COUNTY" ? 903 : AgencyName == "DISTRICT ATTORNEY QNS COUNTY" ? 904 : AgencyName == "DISTRICT ATTORNEY RICHMOND COU" ? 905 : AgencyName == "FINANCIAL INFO SVCS AGENCY" ? 127 : AgencyName == "FIRE DEPARTMENT" ? 57 : AgencyName == "HUMAN RIGHTS COMMISSION" ? 226 : AgencyName == "LANDMARKS PRESERVATION COMM" ? 136 : AgencyName == "LAW DEPARTMENT" ? 25 : AgencyName == "NYC DEPT OF VETERANS' SERVICES" ? 63 : AgencyName == "NYC HOUSING AUTHORITY" ? 996 : AgencyName == "OFFICE OF EMERGENCY MANAGEMENT" ? 17 : AgencyName == "OFFICE OF LABOR RELATIONS" ? 214 : AgencyName == "OFFICE OF MANAGEMENT & BUDGET" ? 19 : AgencyName == "OFFICE OF THE ACTUARY" ? 8 : AgencyName == "OFFICE OF THE COMPTROLLER" ? 15 : AgencyName == "OFFICE OF THE MAYOR" ? 2 : AgencyName == "POLICE DEPARTMENT" ? 56 : AgencyName == "PRESIDENT BOROUGH OF MANHATTAN" ? 10 : AgencyName == "PUBLIC ADVOCATE" ? 101 : AgencyName == "TAX COMMISSION" ? 21 : AgencyName == "TAXI & LIMOUSINE COMMISSION" ? 156 : AgencyName == "TEACHERS RETIREMENT SYSTEM" ? 41 : PayrollNumber
Execute the package to load staging Tables with Data
Staging Tables are populated with Data
Conditional split overview
